/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package onpu_dms_xls.kernel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.channels.FileChannel;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import onpu_dms_xls.exceptions.MyException;
import onpu_dms_xls.exceptions.MySQLException;
import onpu_dms_xls.exceptions.MyXLSException;
import onpu_dms_xls.exceptions.MyXMLException;
import onpu_dms_xls.kernel.common.*;
import onpu_dms_xls.kernel.common.XLS.XLS_Variables;
import onpu_dms_xls.kernel.common.logAndDebug.LoggingFacade;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;

/**
 *
 * @author kovbasiuk
 */
public class File_From_DB_XLS extends File_From_DB{
    
    public File_From_DB_XLS (String inputXML, Connection con) throws MyException{
        super(inputXML, con);
    }

    @Override
    protected void doFileTypeRelatedProcessing() throws MyException{
            LoggingFacade.trace.info("_______________________This is not PDF, working further");
                try {
                    FileChannel ic = new FileInputStream(templateXLS.getAbsolutePath()).getChannel();
                    FileChannel oc = new FileOutputStream(input.getConstantParam_outputFolder() + '/' + TEMPORARY_XLS_FILE_NAME + ".xls").getChannel();
                    ic.transferTo(0, ic.size(), oc);
                    ic.close();
                    oc.close();
                } catch (IOException e) {
                    throw new MyException(
                            "",
                            e);
                }

                res = new File(input.getConstantParam_outputFolder() + '/' + TEMPORARY_XLS_FILE_NAME + ".xls");
                LoggingFacade.trace.info("resulting XLS file location:" + res.getAbsolutePath());

                //write simple data to XLS
                //HERE, the select query for read_from_db vars is called!!
                fillXLSFileWithNoIterableData(res, varDeclArray, conn);
                //realizing DB_Compound type!!!
                boolean DB_Compound_Exists = false;
                for (VariableDeclaration vd : varDeclArray) {
                    if (vd.getVarType().getDB_Relation().equals(VariableType_DBRelationTypes.COMPOUND)) {
                        DB_Compound_Exists = true;
                        LoggingFacade.trace.info("found a DB_Compound Variable:" + vd.getVarName());
                        break;
                    }
                }
                if (DB_Compound_Exists) {
                    varDeclArray = iterateCompoundVariableInVarDeclAndInXLS(varDeclArray, res, conn);
                }

                //write iteration data to XLS
                try {
                    fillXLSFileWithIterableData(res, varDeclArray, conn);
                } catch (Exception e) {
                    LoggingFacade.trace.error("fillXLSFileWithIterableData failure", e);
                    res = null;
                    return;
                }

                // згенерувати імя вихідного ХЛС по шаблону...
                String resultXLS_FileName = input.getConstantParam_outputFileName();
                File temp = new File(input.getConstantParam_outputFolder() + '/' + resultXLS_FileName + ".xls");
                if (temp.canRead()) {
                    temp.delete();
                }
                res.renameTo(temp);
                File old = new File(input.getConstantParam_outputFolder() + '/' + TEMPORARY_XLS_FILE_NAME + ".xls");
                old.delete();

                res = new File(input.getConstantParam_outputFolder() + '/' + resultXLS_FileName + ".xls");
    }


    private void fillXLSFileWithNoIterableData(File XLSFile, VariableDeclaration[] varDeclArray, Connection conn)
            throws MyException {
        //read variables from XLS
        XLS_Variables XLSVars = new XLS_Variables(XLSFile);
        //next, TWO LOOPS!!!!!
        //one - for variable, that get their value from DB
        //second - for variables with values from input or with default value

        for (VariableDeclaration varDec : varDeclArray) {
            if (!varDec.isInIteration()
                    && varDec.getVarType().getDB_Relation().equals(VariableType_DBRelationTypes.SELECT)
                    && varDec.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.VISIBLE)) {
                //1. getQuery
                String fromDBQuery = varDec.getFromDBQuery();
                //2. replace $ parameters
                LoggingFacade.trace.info("fromDBQuery BEFORE REPLACEMENT:" + fromDBQuery);
                fromDBQuery = Util.fillQueryWithVariablesValues(fromDBQuery, varDeclArray);
                LoggingFacade.trace.info("fromDBQuery AFTER REPLACEMENT:" + fromDBQuery);
                //3. execute Query, get variable value ----------temp - dataTypes things....
                String queryResult = "";
                try {
                    Statement st = conn.createStatement();
                    ResultSet rs = st.executeQuery(fromDBQuery);
                    rs.next();
                    queryResult = rs.getString(1);

                } catch (SQLException e) {
                    //that happens
                    if (e.getMessage().equals("ResultSet not positioned properly, perhaps you need to call next.")) {
                        queryResult = "n/d";
                    } else {
                        throw new MySQLException(
                                "XLS_From_DB",
                                "fillXLSFileWithNoIterableData",
                                fromDBQuery,
                                "",
                                e.getMessage());
                    }
                }
                LoggingFacade.trace.info("queryResult:" + queryResult);

                //4. throw it to XLS_Variables_setter
                try {
                    varDec.setValue(queryResult);
                    XLSVars.writeVariableToFile(XLSFile, varDec, queryResult);
                } catch (MyXLSException e) {
                    throw e;
                } catch (Exception e) {
                    throw new MyXLSException(
                            "XLS_From_DB",
                            "fillXLSFileWithNoIterableData",
                            "XLSVars.writeVariableToFile(varDec, queryResult);",
                            e.getMessage());
                }
            }
        }


        for (VariableDeclaration varDec : varDeclArray) {
            if (!varDec.isInIteration()
                    && varDec.getVarType().getDB_Relation().equals(VariableType_DBRelationTypes.NONE)
                    && varDec.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.VISIBLE)) {

                if (!varDec.isValueSet()) {
                    varDec.setValue(varDec.getDefaultValue());
                }
                //throw it to XLS_Variables_setter
                try {
                    XLSVars.writeVariableToFile(XLSFile, varDec, varDec.getValue());
                } catch (MyXLSException e) {
                    throw e;
                } catch (Exception e) {
                    throw new MyXLSException(
                            "XLS_From_DB",
                            "fillXLSFileWithNoIterableData",
                            "XLSVars.writeVariableToFile(varDec, queryResult);",
                            e.getMessage());
                }
            }
        }
        XLSVars.closeConnectionToXLSFile(XLSFile);
    }


    private VariableDeclaration[] iterateCompoundVariableInVarDeclAndInXLS(VariableDeclaration[] varDeclArray, File XLSFile, Connection conn)
            throws MyException {
        LoggingFacade.trace.info("iterateCompoundVariableInVarDeclAndInXLS");
        /*
        1. execute query, get col number
        2. copy/paste variable in XLS and in varDeclArray (one name!)
         */
        VariableDeclaration compoundVariable = null;
        VariableDeclaration compVarNameBuffer = new VariableDeclaration();
        for (VariableDeclaration vd : varDeclArray) {
            if (vd.getVarType().getDB_Relation().equals(VariableType_DBRelationTypes.COMPOUND)) {
                compoundVariable = vd;
                compVarNameBuffer.setVarName(compoundVariable.getVarName());

            }
        }

        LoggingFacade.trace.info("Compound variable" + compoundVariable);

        int colCount;
        try {
            Statement st = conn.createStatement();
            //st.execute(compoundVariable.getIterationRowSingleFromDBQuery());
            ResultSet rs = st.executeQuery(compoundVariable.getIterationRowSingleFromDBQuery());
            colCount = rs.getMetaData().getColumnCount();
            st.close();
        } catch (SQLException e) {
            throw new MyException("XLS_From_DB",
                    "iterateCompoundVariableInVarDeclAndInXLS",
                    "executing query, error:" + e.getMessage());
        }

        if (colCount == 0) {
            throw new MyException("XLS_From_DB",
                    "iterateCompoundVariableInVarDeclAndInXLS",
                    "colCount=0");
        }

        LoggingFacade.trace.info("Col count: " + colCount);

        //#1

        //replace variable with variable0 both in XLS and VarDecl
        InputStream inp;
        HSSFWorkbook wb;
        HSSFSheet sheet;
        try {
            inp = new FileInputStream(XLSFile.getAbsoluteFile());
            wb = new HSSFWorkbook(inp);
            sheet = wb.getSheetAt(0);
        } catch (IOException e) {
            throw new MyXLSException(
                    "XLS_Variables",
                    "XLS_Variables",
                    "error while opening XLS result file",
                    e.getMessage());
        }

        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    String cellVal = cell.getRichStringCellValue().getString();
                    if (cellVal.equals("$" + compoundVariable.getVarName())) {
                        int rowNum = cell.getRowIndex();
                        int colNum = cell.getColumnIndex();
                        sheet.getRow(rowNum).getCell(colNum).setCellValue("$" + compoundVariable.getVarName() + 0);
                    }
                }
            }
        }

        //the only compound variable in varDecl is now changing it's name to variable0
        for (int i = 0; i < varDeclArray.length; i++) {
            VariableDeclaration vd = varDeclArray[i];
            if (vd.getVarType().getDB_Relation().equals(VariableType_DBRelationTypes.COMPOUND)) {
                varDeclArray[i].setVarName(compoundVariable.getVarName() + 0);
                VariableType temp = varDeclArray[i].getVarType();
                temp.setDB_Relation(VariableType_DBRelationTypes.SELECT);

                varDeclArray[i].setVarType(temp);
            }
        }

        //0 variable is set
        LoggingFacade.trace.info("value for 0 variable is set");
        //to add new variables to VarDeclArr I now must create an ArrayList
        ArrayList<VariableDeclaration> varDeclArList = new ArrayList<VariableDeclaration>();
        for (VariableDeclaration vd : varDeclArray) {
            varDeclArList.add(vd);
        }

        for (int i = 1; i < colCount; i++) {
            int a = i - 1;
            String aInSTR = Integer.toString(a);
            String currentLastVarName = "$" + compVarNameBuffer.getVarName() + aInSTR;

            //1 XLS
            for (Row row : sheet) {
                for (Cell cell : row) {
                    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                        String cellVal = cell.getRichStringCellValue().getString();
                        if (cellVal.equals(currentLastVarName)) {
                            LoggingFacade.trace.info("currentLastVarName:" + currentLastVarName);
                            int rowNum = cell.getRowIndex();
                            int colNum = cell.getColumnIndex();
                            LoggingFacade.trace.info("setting cell value to cell, row:" + rowNum + " col:" + colNum);
                            sheet.getRow(rowNum).getCell(colNum + 1).setCellValue("$" + compVarNameBuffer.getVarName() + i);
                        }
                    }
                }
            }

            //2 VarDecl
            VariableDeclaration newVar = new VariableDeclaration();

            VariableType newVarVarType = new VariableType();
            newVarVarType.setDB_Relation(VariableType_DBRelationTypes.SELECT);
            newVarVarType.setInput_Relation(VariableType_InputRelationTypes.NEVER_IN_INPUT);
            newVarVarType.setXLS_Visibility(VariableType_XLSVisibilityTypes.VISIBLE);

            newVar.setVarName(compVarNameBuffer.getVarName() + i);
            newVar.setDataType(VariableDataTypes.STRING);
            newVar.setVarType(newVarVarType);
            newVar.setIsInIteration(true);
            newVar.setIterationRowSingleFromDBQuery(compoundVariable.getIterationRowSingleFromDBQuery());
            LoggingFacade.trace.info("adding new var to varDeclArr:");
            LoggingFacade.trace.info(newVar.toString());
            varDeclArList.add(newVar);
        }


        //вручну провести імена колонок
        LoggingFacade.trace.info("getting iterRow");
        int iterRow = 0;
        for (VariableDeclaration vd : varDeclArList) {
            if (vd.isInIteration()) {
                for (Row row : sheet) {
                    for (Cell cell : row) {
                        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                            if (cell.getRichStringCellValue().getString().equals("$" + vd.getVarName())) {
                                LoggingFacade.trace.info("found in XLS variable " + vd);
                                iterRow = cell.getRowIndex();
                                LoggingFacade.trace.info("iterRow:" + iterRow);

                            }
                        }
                    }
                }
            }
        }
        if (iterRow == 0) {
            throw new MyException("", "", "(iterRow==0)");
        }
        Util.copyRow(wb, sheet, iterRow, iterRow + 1);

        try {
            Statement st = conn.createStatement();
            //st.execute(compoundVariable.getIterationRowSingleFromDBQuery());
            ResultSet rs = st.executeQuery(compoundVariable.getIterationRowSingleFromDBQuery());
            colCount = rs.getMetaData().getColumnCount();
            for (int i = 0; i < colCount; i++) {
                try {
                    sheet.getRow(iterRow).getCell(i).setCellValue(rs.getMetaData().getColumnName(i + 1));
                    CellStyle cs = wb.createCellStyle();
                    cs.setBorderBottom(CellStyle.BORDER_THIN);
                    cs.setBorderLeft(CellStyle.BORDER_THIN);
                    cs.setBorderRight(CellStyle.BORDER_THIN);
                    cs.setBorderTop(CellStyle.BORDER_THIN);
                    cs.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                    cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
                    sheet.getRow(iterRow).getCell(i).setCellStyle(cs);
                } catch (Exception e) {
                    throw new MyException("", "", e.toString());
                }
            }
        } catch (SQLException e) {
            throw new MyException("XLS_From_DB",
                    "iterateCompoundVariableInVarDeclAndInXLS",
                    "executing query, error:" + e.getMessage());
        }


        try {
            FileOutputStream fileOut = new FileOutputStream(XLSFile.getAbsolutePath());
            wb.write(fileOut);
            fileOut.close();
            inp.close();
        } catch (Exception e) {
            throw new MyXLSException(
                    "XLS_Variables",
                    "XLS_Variables",
                    "error while inp.close();",
                    "");
        }


        LoggingFacade.trace.info("End of compound processing");
        return varDeclArList.toArray(new VariableDeclaration[]{});
    }

    private void fillXLSFileWithIterableData(File XLSFile, VariableDeclaration[] varDeclArray, Connection conn)
            throws MyException, SQLException {
        //read variables from XLS
        XLS_Variables XLSVars = new XLS_Variables(XLSFile);

        ArrayList<VariableDeclaration> iterRowVarDecl = new ArrayList<VariableDeclaration>();

        for (VariableDeclaration varDec : varDeclArray) {
            if (varDec.isInIteration()
                    && varDec.getVarType().getInput_Relation().equals(VariableType_InputRelationTypes.NEVER_IN_INPUT)
                    && varDec.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.VISIBLE)) {
                iterRowVarDecl.add(varDec);
            }
        }

        if (iterRowVarDecl.size() == 0) {
            throw new MyXMLException(
                    "XLS_From_DB",
                    "fillXLSFileWithIterableData",
                    "no iterable variables found in variable declaration array, formed from template xml",
                    "");
        }

        //1. getQuery
        String fromDBQuery = iterRowVarDecl.get(0).getIterationRowSingleFromDBQuery();

        //2. replace $ parameters
        LoggingFacade.trace.info("fromDBQuery BEFORE REPLACEMENT:" + fromDBQuery);
        fromDBQuery = Util.fillQueryWithVariablesValues(fromDBQuery, varDeclArray);
        LoggingFacade.trace.info("fromDBQuery AFTER REPLACEMENT:" + fromDBQuery);

        //3. execute Query, get variable value ----------temp - dataTypes things....
        ResultSet rs;
        try {
            Statement st = conn.createStatement();
            rs = st.executeQuery(fromDBQuery);
        } catch (SQLException e) {
            throw new MySQLException(
                    "XLS_From_DB",
                    "fillXLSFileWithNoIterableData",
                    fromDBQuery,
                    "",
                    e.getMessage());
        }

        //4. throw it to XLS_Variables_setter
        while (rs.next()) {
            for (VariableDeclaration vd : iterRowVarDecl) {
                LoggingFacade.trace.info(vd.toString());
                String queryRes = rs.getString(iterRowVarDecl.indexOf(vd) + 1);
                if (queryRes == null) {
                    LoggingFacade.trace.info("!!!!!!!!!!! null in QUERY result, setting value=\"\"");
                    queryRes = "";
                }
                vd.setValue(queryRes);
            }
            try {
                XLSVars.appendIterationRowsToXLS(XLSFile, iterRowVarDecl.toArray(new VariableDeclaration[]{}));
            } catch (MyXLSException e) {
                throw e;
            } catch (Exception e) {
                throw new MyXLSException(
                        "XLS_From_DB",
                        "fillXLSFileWithIterableData",
                        "XLSVars.writeVariableToFile(varDec, queryResult);",
                        e.getMessage());
            }
        }

        XLSVars.destroyLastIterationRowLeft(iterRowVarDecl.toArray(new VariableDeclaration[]{}));
        XLSVars.closeConnectionToXLSFile(XLSFile);
    }

}
